	[ο ͺ̽ ]
CREATE  DATABASE DBTEST;


	[ ͺ̽ ]
USE DBTEST;


	[ 7-1]
CREATE TABLE  (
	̵  VARCHAR(20)	 NOT NULL,
	̸    VARCHAR(10)	 NOT NULL,
		    INT,
		    VARCHAR(10)	 NOT NULL,
		    VARCHAR(20),
		    INT   DEFAULT 0,
	PRIMARY KEY(̵)
);


	[ 7-2]
CREATE TABLE  ǰ (
	ǰȣ   CHAR(3)   NOT NULL,
	ǰ     VARCHAR(20),
	     INT,
	ܰ       INT,
	ü   VARCHAR(20),
	PRIMARY KEY(ǰȣ),
	CHECK ( >= 0 AND  <=10000)
);


	[ 7-3]
CREATE TABLE  ֹ (
	ֹȣ   CHAR(3) NOT NULL,
	ֹ   VARCHAR(20),
	ֹǰ   CHAR(3),
	       INT,
	     VARCHAR(30),
	ֹ   DATE,
	PRIMARY KEY(ֹȣ),
	FOREIGN KEY(ֹ)   REFERENCES   (̵),
	FOREIGN KEY(ֹǰ)   REFERENCES   ǰ(ǰȣ)
);


	[ 7-4]
CREATE TABLE  ۾ü (
	üȣ   CHAR(3) NOT NULL,
	ü     VARCHAR(20),
	ּ       VARCHAR(100),
	ȭȣ   VARCHAR(20),
	PRIMARY KEY(üȣ)
);


	[ 7-5]
ALTER TABLE  ADD Գ¥ DATE; 


	[ 7-6]
ALTER TABLE  DROP COLUMN Գ¥;


	[ 7-7] 
ALTER TABLE  ADD CONSTRAINT CHK_AGE CHECK( >= 20); 


	[ 7-8] 
ALTER TABLE  DROP CONSTRAINT CHK_AGE; 


	[ 7-9]
DROP TABLE ۾ü; 


	[ 7-10 ]
[ ̺  ]
INSERT INTO  VALUES ('apple', 'ȭ', 20, 'gold', 'л', 1000);
INSERT INTO  VALUES ('banana', '輱', 25, 'vip', 'ȣ', 2500);
INSERT INTO  VALUES ('carrot', '', 28, 'gold', '', 4500);
INSERT INTO  VALUES ('orange', '', 22, 'silver', 'л', 0);
INSERT INTO  VALUES ('melon', '', 35, 'gold', 'ȸ', 5000);
INSERT INTO  VALUES ('peach', '', NULL, 'silver', 'ǻ', 300);
INSERT INTO  VALUES ('pear', 'ä', 31, 'silver', 'ȸ', 500);

[ǰ ̺  ]
INSERT INTO ǰ VALUES ('p01', '׳ɸ', 5000, 4500, 'ѽǰ');
INSERT INTO ǰ VALUES ('p02', 'ſ̸', 2500, 5500, 'αǪ');
INSERT INTO ǰ VALUES ('p03', '', 3600, 2600, 'Ѻ');
INSERT INTO ǰ VALUES ('p04', 'ݸ', 1250, 2500, 'Ѻ');
INSERT INTO ǰ VALUES ('p05', 'ū', 2200, 1200, 'ѽǰ');
INSERT INTO ǰ VALUES ('p06', '쵿', 1000, 1550, 'αǪ');
INSERT INTO ǰ VALUES ('p07', '޺Ŷ', 1650, 1500, 'Ѻ');

[ֹ ̺  ]
INSERT INTO ֹ VALUES ('o01', 'apple', 'p03', 10, ' ', '22/01/01');
INSERT INTO ֹ VALUES ('o02', 'melon', 'p01', 5, 'õ 籸', '22/01/10');
INSERT INTO ֹ VALUES ('o03', 'banana', 'p06', 45, '⵵ õ', '22/01/11');
INSERT INTO ֹ VALUES ('o04', 'carrot', 'p02', 8, 'λ ', '22/02/01');
INSERT INTO ֹ VALUES ('o05', 'melon', 'p06', 36, '⵵ ν', '22/02/20');
INSERT INTO ֹ VALUES ('o06', 'banana', 'p01', 19, 'ûϵ ', '22/03/02');
INSERT INTO ֹ VALUES ('o07', 'apple', 'p03', 22, ' ', '22/03/15');
INSERT INTO ֹ VALUES ('o08', 'pear', 'p02', 50, ' õ', '22/04/10');
INSERT INTO ֹ VALUES ('o09', 'banana', 'p04', 15, '󳲵 ', '22/04/11');
INSERT INTO ֹ VALUES ('o10', 'carrot', 'p03', 20, '⵵ Ⱦ', '22/05/22');


	[ 7-10]
SELECT   ̵, ̸, 
FROM     ;


	[ 7-11]
SELECT   ̵, ̸, , , , 
FROM     ;


	[ 7-12]
SELECT    *
FROM     ;


	[ 7-13]
SELECT   ü
FROM     ǰ;


	[ 7-14]
SELECT    ALL  ü
FROM     ǰ;


	[ 7-15]
SELECT    DISTINCT   ü
FROM     ǰ;


	[ 7-16]
SELECT   ǰ, ܰ AS 
FROM     ǰ;


	[ 7-17]----> MySQL     
SELECT   ǰ, ܰ + 500 AS " ܰ"
FROM     ǰ;


SELECT   ǰ, ܰ + 500 AS ' ܰ'
FROM     ǰ;


	[ 7-18]
SELECT   ǰ, , ܰ
FROM     ǰ
WHERE    ü = 'Ѻ'; 


	[ 7-19]
SELECT   ֹǰ, , ֹ
FROM     ֹ
WHERE    ֹ = 'apple'  AND   >= 15;


	[ 7-20]
SELECT   ֹǰ, , ֹ, ֹ
FROM     ֹ
WHERE    ֹ = 'apple'  OR   >= 15;


	[ 7-21]
SELECT   ǰ, ܰ, ü
FROM     ǰ
WHERE    ܰ >= 2000  AND  ܰ <= 3000;


	[ 7-22]
SELECT   ̸, , , 
FROM     
WHERE    ̸ LIKE '%';


	[ 7-23]
SELECT   ̵, ̸, 
FROM     
WHERE    ̵ LIKE '_____';


	[ 7-24]
SELECT   ̸
FROM     
WHERE     IS NULL;


	[ 7-25]
SELECT   ̸
FROM     
WHERE     IS NOT NULL;


	[ 7-26]
SELECT   ̸, , 
FROM     
ORDER BY     DESC;


	[ 7-27]
SELECT  ֹ, ֹǰ, , ֹ
FROM    ֹ
WHERE    >= 10
ORDER BY  ֹǰ ASC,  DESC;


	[ 7-28]
SELECT   AVG(ܰ)
FROM    ǰ;


	[ 7-29]----> MySQL     
SELECT   SUM()  AS  " հ"
FROM    ǰ
WHERE   ü = 'Ѻ';


SELECT   SUM()  AS  ' հ'
FROM    ǰ
WHERE   ü = 'Ѻ';


	[ 7-30]
SELECT   COUNT(̵) AS 
FROM    ;

SELECT   COUNT() AS 
FROM    ;

SELECT   COUNT(*) AS 
FROM    ;


	[ 7-31]----> MySQL    
SELECT   COUNT(DISTINCT ü) AS "ü "
FROM    ǰ;

SELECT   COUNT(DISTINCT ü) AS 'ü ' 
FROM    ǰ;


	[ 7-32]
SELECT   ֹǰ, SUM() AS ֹ
FROM     ֹ
GROUP BY   ֹǰ;

SELECT   SUM() AS ֹ
FROM     ֹ
GROUP BY   ֹǰ;


	[ 7-33]
SELECT   ü, COUNT(*) AS ǰ, MAX(ܰ) AS ְ
FROM     ǰ
GROUP BY   ü;


	[ 7-34]
SELECT   ü, COUNT(*) AS ǰ, MAX(ܰ) AS ְ
FROM     ǰ
GROUP BY  ü  HAVING COUNT(*) >= 3;


	[ 7-35]
SELECT   , COUNT(*) AS , AVG() AS 
FROM     
GROUP BY    HAVING AVG() >= 1000;


	[ 7-36]
SELECT   ֹǰ, ֹ, SUM() AS ֹ
FROM     ֹ
GROUP BY  ֹǰ, ֹ; 


	[ 7-37]
SELECT   ǰ.ǰ
FROM     ǰ, ֹ
WHERE    ֹ.ֹ = 'banana'  AND  ǰ.ǰȣ = ֹ.ֹǰ; 


	[ 7-38]
SELECT   ֹ.ֹǰ, ֹ.ֹ
FROM     , ֹ
WHERE    . >= 30  AND  .̵ = ֹ.ֹ; 

SELECT  o.ֹǰ, o.ֹ
FROM      c, ֹ o
WHERE   c. >= 30  AND  c.̵ = o.ֹ; 

SELECT  ֹ.ֹǰ, ֹ.ֹ
FROM      INNER JOIN  ֹ  ON  .̵ = ֹ.ֹ
WHERE   . >= 30;


	[ 7-39]
SELECT  ǰ.ǰ
FROM    , ǰ, ֹ
WHERE   .̸ = '' AND  .̵ = ֹ.ֹ 
		AND  ǰ.ǰȣ = ֹ.ֹǰ; 


[ܺ  ]
SELECT	 .̸, ֹ.ֹǰ, ֹ.ֹ
FROM	   LEFT OUTER JOIN  ֹ  ON  .̵ = ֹ.ֹ;

SELECT	 .̸, ֹ.ֹǰ, ֹ.ֹ
FROM	 ֹ  RIGHT OUTER JOIN    ON  ֹ.ֹ = .̵;


	[ 7-40]
SELECT   ǰ, ܰ
FROM     ǰ
WHERE    ü = (SELECT   ü
                     FROM    ǰ
                     WHERE  ǰ = '޺Ŷ');

SELECT   ǰ, ܰ
FROM     ǰ
WHERE    ü = 'Ѻ';


	[ 7-41]
SELECT  ̸, 
FROM    
WHERE    = (SELECT  MAX()  FROM  ); 

SELECT  ̸, 
FROM    
WHERE    =5000; 


	[ 7-42]
SELECT  ǰ, ü
FROM    ǰ
WHERE   ǰȣ  IN (SELECT  ֹǰ   
		      FROM   ֹ
		      WHERE  ֹ  = 'banana'); 

SELECT  ǰ, ü
FROM    ǰ
WHERE   ǰȣ  IN  ('p01', 'p04', 'p06'); 



[ Ǹ ̿ SELECT ]
SELECT	 ǰ.ǰ, ǰ.ü
FROM	 ǰ, ֹ
WHERE	 ǰ.ǰȣ = ֹ.ֹǰ  AND  ֹ.ֹ  = 'banana'; 


[EXISTS ڸ ̿ SELECT ]
SELECT	 ǰ, ü
FROM	 ǰ
WHERE	 EXISTS  (SELECT  *   
                  FROM   ֹ
                  WHERE  ǰ.ǰȣ = ֹ.ֹǰ  
		            AND  ֹ.ֹ  = 'banana'); 


	[ 7-43]
SELECT  ǰ, ü
FROM    ǰ
WHERE   ǰȣ  NOT  IN (SELECT	  ֹǰ   
			   FROM	  ֹ
			   WHERE	  ֹ  = 'banana'); 


	[ 7-44]
SELECT   ǰ, ܰ, ü
FROM    ǰ
WHERE   ܰ  > ALL (SELECT   ܰ   
		     FROM     ǰ
		     WHERE    ü  = 'ѽǰ'); 


	[ 7-45]
SELECT  ̸
FROM    
WHERE   EXISTS (SELECT  *   
		FROM    ֹ
		WHERE   ֹ = '2022-03-15' 
		          AND  ֹ.ֹ = .̵); 


	[ 7-46]
SELECT  ̸
FROM    
WHERE   NOT  EXISTS (SELECT  *   
		     FROM    ֹ
		     WHERE   ֹ = '2022-03-15' 
  	       	               AND  ֹ.ֹ = .̵); 


	[ 7-47]
INSERT  
INTO    (̵, ̸, , , , )
VALUES ('strawberry', '', 30, 'vip', '', 100);

SELECT  *  FROM  ;  


	[ 7-48]
INSERT  
INTO    (̵, ̸, , , )
VALUES ('tomato', '', 36, 'gold', 4000);

SELECT  *  FROM  ;  


[μ ǹ ̿  ]
CREATE TABLE  Ѻǰ (
	ǰ      VARCHAR(20),
	      INT,
	ܰ        INT,
	PRIMARY KEY(ǰ)
);

INSERT  
INTO    Ѻǰ(ǰ, , ܰ)
SELECT  ǰ, , ܰ
FROM    ǰ
WHERE   ü = 'Ѻ';

SELECT  *  FROM  Ѻǰ;  


	[ 7-49]
UPDATE   ǰ  
SET      ǰ = 'ū'
WHERE    ǰȣ = 'p03';

SELECT  *  FROM  ǰ;  


	[ 7-50]
UPDATE  ǰ  
SET     ܰ = ܰ * 1.1;

SELECT  *  FROM  ǰ;  


	[ 7-51]
UPDATE  ֹ  
SET      = 5
WHERE   ֹ IN (SELECT    ̵
 		     FROM      
		     WHERE     ̸ = 'ȭ');

SELECT  *  FROM  ֹ; 


	[ 7-52]
DELETE
FROM    ֹ  
WHERE   ֹ = '2022-05-22';

SELECT  *  FROM  ֹ; 


	[ 7-53]
DELETE     
FROM    ֹ
WHERE   ֹ IN (SELECT  ̵
 	             FROM    
		     WHERE   ̸ = 'ȭ');

SELECT  *  FROM  ֹ;  


	[ 7-54]
DELETE
FROM	 ֹ;

SELECT  *  FROM  ֹ; 


	[ 7-55]
CREATE VIEW     (̵, ̸, , )
AS SELECT       ̵, ̸, , 
   FROM         
   WHERE         = 'vip'
WITH CHECK OPTION;

SELECT  *  FROM    ;


CREATE VIEW       
AS SELECT         ̵, ̸, , 
   FROM           
   WHERE           = 'vip'
WITH CHECK OPTION;


	[ 7-56]----> MySQL  ȵ(üǰó  Ұ 並    , ,   Ұϱ  MySQL with check option  )
CREATE  VIEW   üǰ(ü, ǰ)
AS  SELECT     ü, COUNT(*)
    FROM       ǰ
    GROUP BY   ü
WITH CHECK OPTION;


()---->    with check option  ۼؾ 

CREATE  VIEW   üǰ(ü, ǰ)
AS  SELECT     ü, COUNT(*)
    FROM       ǰ
    GROUP BY   ü; 

SELECT  *  FROM	   üǰ;


	[ 7-57]
SELECT  *  FROM    WHERE   >= 20;


CREATE  VIEW   ǰ1
AS SELECT  ǰȣ, , ü
   FROM    ǰ
WITH CHECK OPTION;

SELECT  *  FROM  ǰ1;


CREATE  VIEW   ǰ2
AS SELECT   ǰ, , ü
   FROM     ǰ
WITH CHECK OPTION;

SELECT  *  FROM  ǰ2;


	[ 7-58]
INSERT  INTO   ǰ1   VALUES ('p08', 1000, 'żǰ');

SELECT  *  FROM   ǰ1; 

SELECT  *  FROM  ǰ;


	[ 7-59]
DROP VIEW ;